﻿using BLevy.Datos.Interfases;
using BLevy.Entidad;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DapperExtensions;
using Dapper;

namespace BLevy.Datos.Repositories
{
    public interface IRolRepository : IRepository<Rol>
    {
        int AddPermisosRol(PermisoRol permisos);
        PermisoRol FindPermisosByIdRol(int rol_id);
        IList<Rol> FindAllConPermisos();
        int ModificarPermisos(PermisoRol permisos);
    }

    public class RolRepository : IRolRepository
    {
        protected readonly IDbConnection _db;

        public RolRepository()
        {
            try
            {
                _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString());
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IList<Rol> FindAll()
        {
            const string query = "SELECT * FROM ROLES";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Rol>(query).ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IQueryable<Rol> Find(System.Linq.Expressions.Expression<Func<Rol, bool>> predicate)
        {
            throw new NotImplementedException();
        }

        public Rol FindById(int id)
        {
            const string query = "SELECT * FROM ROLES " +
                "WHERE ID = @id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Rol>(query, new { id = id }).SingleOrDefault();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int Add(Rol newEntity)
        {
            const string query = "INSERT INTO ROLES (ROL, ACTIVO) " +
                "VALUES (@rol, @activo); " +
                "SELECT LAST_INSERT_ID()";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<int>(query, new
                    {
                        rol = newEntity.rol,
                        activo = newEntity.activo
                    }).Single();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int Remove(int id)
        {
            const string query = "UPDATE ROLES SET ACTIVO = 0 " +
                "WHERE ID = @id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new { id = id });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int Modify(Rol entity)
        {
            const string query = "UPDATE ROLES SET ROL = @rol, ACTIVO = @activo " +
                "WHERE ID = @id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new { rol = entity.rol, activo = entity.activo, id = entity.id });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int AddPermisosRol(PermisoRol permisos)
        {
            const string query = "INSERT INTO PERMISOSROLES (ROL_ID, LEER, INSERTAR, " +
            "MODIFICAR, ELIMINAR) VALUES (@rol_id, @leer, @insertar, @modificar, @eliminar)";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new
                    {
                        rol_id = permisos.rol_id,
                        leer = permisos.leer,
                        insertar = permisos.insertar,
                        modificar = permisos.modificar,
                        eliminar = permisos.eliminar
                    });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }


        public PermisoRol FindPermisosByIdRol(int rol_id)
        {
            const string query = "SELECT * FROM PERMISOSROLES " +
                "WHERE ROL_ID = @rol_id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<PermisoRol>(query, new { rol_id = rol_id }).SingleOrDefault();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }


        public IList<Rol> FindAllConPermisos()
        {
            const string query = "SELECT * FROM ROLES " +
                "INNER JOIN PERMISOSROLES ON PERMISOSROLES.ROL_ID = ROLES.ID";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Rol, PermisoRol, Rol>(query,
                        (rol, permisos) => { rol.permisos = permisos; return rol; }, splitOn: "id").ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }


        public int ModificarPermisos(PermisoRol permisos)
        {
            const string query = "UPDATE PERMISOSROLES  SET LEER = @leer, INSERTAR = @insertar, " +
            "MODIFICAR = @modificar, ELIMINAR = @eliminar " +
                "WHERE ROL_ID = @rol_id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new
                    {
                        leer = permisos.leer,
                        insertar = permisos.insertar,
                        modificar = permisos.modificar,
                        eliminar = permisos.eliminar,
                        rol_id = permisos.rol_id
                    });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }
    }
}
